insert overwrite table mid_rule1
select A.CARDUSER_ID,B.CUSTOMER_ID,B.customer_types,A.customer_level,B.gender,A.NAME,substr(B.MOBILEPHONE,1,11),B.ADDRESS
,C.ORDERNUM,C.REALAMOUNT,C.PPSORDERNUMBER,C.OILCODE,C.STATIONCODE,C.SENDTIME,C.MEMBERNO
FROM TBL_CARDUSER AS A
JOIN TBL_CUSTOMER AS B
ON A.CUSTOMER_ID = B.CUSTOMER_ID
JOIN TBL_ORDERDETAILS AS C
ON C.CARDUSERID=A.CARDUSER_ID  and C.CUSTOMER_ID=B.CUSTOMER_ID
;


insert overwrite table mid_rule2
select Z.transaction_number,Z.transaction_date,A.station_id,A.mop_name,Y.cashier_name
from mid.transaction_items_mop as A
join transactions_vip AS Z on A.transaction_id=Z.transaction_id and A.station_id=Z.station_id  and A.transaction_date=Z.transaction_date
JOIN mid.cashiers AS Y
on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id
where  A.item_value > 0  ;




insert overwrite table mid_rule3
select
a.CARDUSER_ID    ,
a.CUSTOMER_ID    ,
a.customer_types ,
a.CUSTOMER_LEVEL ,
a.GENDER         ,
a.NAME           ,
a.MOBILEPHONE    ,
a.ADDRESS        ,
a.ORDERNUM       ,
a.REALAMOUNT     ,
a.PPSORDERNUMBER ,
trim(a.OILCODE)        ,
trim(a.STATIONCODE)    ,
a.SENDTIME       ,
a.MEMBERNO       ,

trim(b.mop_name),trim(b.cashier_name),
 case when (SUBSTR(b.SENDTIME,12,5)>='19:00' and SUBSTR(b.SENDTIME,12,5)<='24:00' )
           or (SUBSTR(b.SENDTIME,12,5)>='00:00' and SUBSTR(b.SENDTIME,12,5)<='07:00' )
           then 1 else 0 end  isNight
 from
mid_rule1 as a
join mid_rule2 as b
on trim(a.STATIONCODE)=trim(b.station_id) and
substr(a.SENDTIME,1,10) = substr(b.SENDTIME,1,10) and trim(a.ORDERNUM)=trim(b.ORDERNUM);

insert overwrite table mid_rule4
select
a.CARDUSER_ID    ,
a.CUSTOMER_ID    ,
a.customer_types ,
a.CUSTOMER_LEVEL ,
a.GENDER         ,
a.NAME           ,
a.MOBILEPHONE    ,
a.ADDRESS        ,
a.ORDERNUM       ,
a.REALAMOUNT     ,
a.PPSORDERNUMBER ,
trim(a.OILCODE)        ,
trim(a.STATIONCODE)    ,
a.SENDTIME       ,
a.MEMBERNO       ,
a.mop_name,
a.cashier_name,
a.isNight,
i.yhjsfyq
 from mid_rule3 a
left join
 (select use_trade_no,validity_start,validity_end,percent,case when(datediff(validity_end,validity_start) >=31) then 1 else 0 end yhjsfyq from
coupon_info where percent!='8') i
on  a.PPSORDERNUMBER = i.use_trade_no

insert overwrite table res_rule
SELECT
CARDUSER_ID      ,
CUSTOMER_ID      ,
max( customer_types),
max( customer_level),
max(GENDER)           ,
max(NAME             ),
MOBILEPHONE      ,
max(ADDRESS          ),
concat_ws(',', collect_set(ORDERNUM))  ORDERNUM         ,
sum(REALAMOUNT)/100.0       ,
concat_ws(',', collect_set(PPSORDERNUMBER)) PPSORDERNUMBER   ,
concat_ws(',', collect_set(OILCODE))  OILCODE,
STATIONCODE      ,
SUBSTR(SENDTIME,1,10) SENDTIME         ,
max(MEMBERNO),
concat_ws(',', collect_set(mop_name)) mop_name         ,
concat_ws(',', collect_set(cashier_name)) cashier_name      ,
null,null,count( ORDERNUM),sum(isNight),sum(nvl(yhjsfyq,0))
from mid_rule4
GROUP BY
CARDUSER_ID,CUSTOMER_ID,SUBSTR(SENDTIME,1,10),MOBILEPHONE,STATIONCODE;
